export default class {
    constructor (conn, name) {
        this.conn = conn
        this.name = name
        this.joins = []
        this.wheres = []
    }
    alias(al) {
        this.al = al
        return this
    }
    fields (fields) {
        this.fields_str = fields
        return this
    }
    where (...where) {
        this.wheres.push(where)
        return this
    }
    join (...join) {
        this.joins.push({
            table: join[0],
            on: join[1],
            relation: join[2]?join[2]:'INNER'
        })
        return this
    }
    order (order) {
        this.order_str = order
        return this
    }
    group (group) {
        this.group_str = group
    }
    limit (limit) {
        this.limit_str = limit
        return this
    }
    reset() {
        this.joins.splice(0,this.joins.length)
        this.wheres.splice(0,this.wheres.length)
        this.order_str = undefined
        this.group_str = undefined
        this.limit_str = undefined
        this.fields_str = undefined
        this.al = undefined
    }
    page (page, pageSize, query) {
        let select_sql = 'SELECT ' + (this.fields_str?this.fields_str:' * ')
            + ' FROM ' + this.name + ' ' + (this.al?' as ' + this.al : '') + ' '
        let count_sql = 'SELECT count(1) as num FROM ' + this.name + ' '  + (this.al?' as ' + this.al : '') + ' '
            let join_sql = ''
        if(this.joins) {
            for(let join of this.joins) {
                join_sql += ' ' + join.relation + ' JOIN ' + join.table + ' ON ' + join.on
            }
        }
        select_sql += join_sql
        count_sql += join_sql
        select_sql += ' WHERE 1 = 1 ' + this.get_where(this.wheres)
        count_sql += ' WHERE 1 = 1 ' + this.get_where(this.wheres)
        select_sql += this.order_str?' ORDER BY ' + this.order_str:''
        count_sql += this.group_str?' GROUP BY ' + this.group_str:''
        select_sql += this.group_str?' GROUP BY ' + this.group_str:''
        select_sql += ' LIMIT ' + (pageSize*(page-1)) + ',' + pageSize

        this.reset()
        let ret = {}
        let promise = new Promise((resolve, reject) => {
            this.conn.query(select_sql, [], (err, result) => {
                if(err) {
                    reject(err)
                }else {
                    resolve(result)
                }
            })
        })
        let encode_query = (query)=> {
            let query_arr = []
            for(let key in query) {
                query_arr.push(`${key}=${query[key]}`)
            }
            return query_arr.join('&')
        }
        return new Promise((resolve,reject) => {
            promise.then(res =>{
                this.conn.query(count_sql,[],(err, result) => {
                    if (err) {
                        reject(err)
                    }else {
                        ret.data = res
                        ret.total = result[0].num
                        ret.cur = res.length > 0 ? Number(page) : Number(page) - 1
                        ret.pageSize = pageSize
                        ret.render = (url) => {
                            let html = `<nav><ul class="pagination">`
                            let totalPage = Math.ceil(ret.total*1.0/pageSize)
                            let queryString = encode_query(query)
                             if(ret.cur !== 1) {
                                 html += `<li class="page-item"><a class="page-link" href="${url}?page=${ret.cur - 1}&${queryString}">上一页</a></li>`
                             }
                             if(totalPage <= 8) {
                                 for(let i = 1;i <= totalPage;i++) {
                                     html += `<li class="page-item ${i === ret.cur?'active':''}"><a class="page-link" href="${url}?page=${i}&${queryString}">${i}</a></li>`
                                 }
                             }else {
                                 if(ret.cur + 4 < totalPage) {
                                     for(let i = ret.cur - 2;i < ret.cur + 2;i++ ) {
                                         html += `<li class="page-item ${i === ret.cur?'active':''}"><a class="page-link" href="${url}?page=${i}&${queryString}">${i}</a></li>`
                                     }
                                     html += `<li class="page-item"><a class="page-link" href="#">...</a></li>`
                                     for(let i = totalPage - 2;i <= totalPage;i ++) {
                                         html += `<li class="page-item"><a class="page-link" href="${url}?page=${i}&${queryString}">${i}</a></li>`
                                     }
                                 }else {
                                     for(let i = 1;i < 3;i++ ) {
                                         html += `<li class="page-item"><a class="page-link" href="${url}?page=${i}&${queryString}">${i}</a></li>`
                                     }
                                     html += `<li class="page-item"><a class="page-link" href="#">...</a></li>`
                                     for(let i = totalPage - 3;i <= totalPage;i ++) {
                                         html += `<li class="page-item ${i === ret.cur?'active':''}"><a class="page-link" href="${url}?page=${i}&${queryString}">${i}</a></li>`
                                     }
                                 }
                             }
                            if(ret.cur !== totalPage) {
                                html += `<li class="page-item "><a class="page-link" href="${url}?page=${ret.cur+1}&${queryString}">下一页</a></li>`
                            }
                            html += `</ul></nav>`
                        }
                        resolve(ret)
                    }
                })
            }).catch(err=>{
                reject(err)
            })
        })

    }
    buildSql() {
        let select_sql = 'SELECT ' + (this.fields_str?this.fields_str:' * ')
            + ' FROM ' + this.name +  ' ' +  (this.al?this.al:'') + ' '
        let join_sql = ''
        if (this.joins) {
            for (let join of this.joins) {
                join_sql +=  ' '+ join.relation + ' JOIN ' + join.table + ' ON ' + join.on
            }
        }
        select_sql += join_sql
        select_sql += ' WHERE 1 = 1 ' + this.get_where(this.wheres)
        select_sql += this.order_str?' ORDER BY ' + this.order_str:''
        select_sql += this.group_str?' GROUP BY ' + this.group_str:''
        select_sql += this.limit_str?' LIMIT ' + this.limit_str:''
        this.reset()
        return '(' + select_sql + ')'
    }
    select() {
        let select_sql = 'SELECT ' + (this.fields_str?this.fields_str:' * ')
            + ' FROM ' + this.name + ' ' +  (this.al?this.al:'') + ' '
        let join_sql = ''
        if (this.joins) {
            for (let join of this.joins) {
                join_sql +=  ' ' + join.relation + ' JOIN ' + join.table + ' ON ' + join.on
            }
        }
        select_sql += join_sql
        select_sql += ' WHERE 1 = 1 ' + this.get_where(this.wheres)
        select_sql += this.order_str?' ORDER BY ' + this.order_str:''
        select_sql += this.group_str?' GROUP BY ' + this.group_str:''
        select_sql += this.limit_str?' LIMIT ' + this.limit_str:''
        console.log(`DBSQL:${select_sql}`)
        this.reset()
        return new Promise((resolve,reject) => {
            this.conn.query(select_sql, [],(err, result) => {
                if(err) {
                    reject(err)
                }else {
                    resolve(result)
                }
            })
        })
    }
    delete() {
        let delete_sql = 'DELETE FROM ' + this.name + ' WHERE 1 = 1 ' + this.get_where(this.wheres)
        console.log(delete_sql)
        this.reset()
        return new Promise((resolve, reject) => {
            this.conn.query(delete_sql, [], (err, result) => {
                if(err) {
                    reject(err)
                }else {
                    resolve(result)
                }
            })
        })
    }
    find() {
        let select_sql = 'SELECT ' + (this.fields_str?this.fields_str:' * ')
            + ' FROM ' + this.name + ' ' +  (this.al?this.al:'') + ' '
        let join_sql = ''
        if (this.joins) {
            for (let join of this.joins) {
                join_sql += ' ' + join.relation + ' JOIN ' + join.table + ' ON ' + join.on
            }
        }
        select_sql += join_sql
        select_sql += ' WHERE 1 = 1 ' + this.get_where(this.wheres)
        select_sql += this.order_str?' ORDER BY ' + this.order_str:''
        select_sql += this.group_str?' GROUP BY ' + this.group_str:''
        select_sql += ' LIMIT  1'
        console.log(select_sql)
        this.reset()
        return new Promise((resolve,reject) => {
            this.conn.query(select_sql, [], (err,result) => {
                if(err) {
                    reject(err)
                }else {
                    resolve(result.length > 0?result[0]:undefined)
                }
            })
        })
    }
    insert(obj) {
        let {sql, values} = this.get_insert_statement(obj)
        this.reset()
        console.log(sql)
        return new Promise((resolve, reject) => {
            this.conn.query(sql, values, (err, result) => {
                if(err) {
                    reject(err)
                }else {
                    resolve(result)
                }
            })
        })
    }
    insertAll(arr) {
        let sql = this.get_insert_all_statement(arr)
        this.reset()
        return new Promise((resolve, reject) => {
            this.conn.query(sql, [], (err, result) => {
                if(err) {
                    reject(err)
                }else {
                    resolve(result)
                }
            })
        })
    }

    update(obj) {
        let update_sql = this.get_update_statement(obj,this.wheres)
        console.log(update_sql)
        this.reset()
        return new Promise((resolve, reject) => {
            this.conn.query(update_sql, [], (err, result) => {
                if(err) {
                    reject(err)
                }else {
                    resolve(result)
                }
            })
        })
    }
    get_update_statement (obj, wheres) {
        let ret = 'UPDATE ' + this.name + ' SET '
        for (let field in obj) {
            ret += '`' + field + '`' + ' = \'' + obj[field] + '\','
        }
        ret = ret.substr(0, ret.length - 1)
        ret +=  ' WHERE 1 = 1 '
        ret += this.get_where(wheres)
        return ret
    }
    get_insert_statement (obj) {
        let sql = 'INSERT INTO ' + this.name,
            field_arr = [],
            tmp = [],
            values = []
        for (let key in obj) {
            field_arr.push(key)
            tmp.push('?')
            values.push(obj[key])
        }
        sql += '( `' + field_arr.join('`,`') + '`) VALUES (' + tmp.join(',') + ')'
        return { sql, values }
    }
    get_insert_all_statement(arr) {
        let field_arr = []
        let values_arr = []
        for(let key in arr[0]) {
            field_arr.push(key)
        }
        arr.forEach(val => {
            let varr = []
            for(let _ of field_arr) {
                varr.push(`'${val[_]}'`)
            }
            values_arr.push(`(${varr.join(',')})`)
        })
        return `INSERT INTO ${this.name} (${field_arr.join(',')}) VALUES ${values_arr.join(',')}`
    }

    get_where (wheres) {
        let ret = ''
        for (let where of wheres) {
            if (!where instanceof Array) {
                ret += ' AND ' + where
            }else {
                if (where[0].trim() == 'and' || where[0].trim() == 'or') {
                    if (where.length == 2) {
                        ret += ' ' + where[0] + ' ' + where[1]
                    }else if (where.length == 3) {
                        ret += ' ' + where[0] + ' ' + where[1] + ' = \'' + where[2] + '\''
                    }else if (where.length == 4) {
                        if(where[2].trim() === 'in')
                            ret += ' ' + where[0] + ' ' + where[1] + ' ' + where[2] + ' ' + '(' + where[3] + ')'
                        else
                            ret += ' ' + where[0] + ' ' + where[1] + ' ' + where[2] + ' ' + '\'' + where[3] + '\''
                    }
                }else if (where.length == 2) {
                    ret += ' AND ' + where[0] + ' = \'' + where[1] + '\''
                }else if (where.length == 3) {
                    console.log(where[1])
                    if(where[1].trim() === 'in')
                        ret += 'AND ' + where[0] + ' ' + where[1] + ' (' + where[2] + ')'
                    else
                        ret += 'AND ' + where[0] + ' ' + where[1] + ' \'' + where[2] + '\''
                }
            }
        }
        return ret
    }
}
